IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[DeleteQuestionnaires]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[DeleteQuestionnaires]
GO

SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO

CREATE PROCEDURE [dbo].[DeleteQuestionnaires]  (
@ids 			varchar(1000)
)

AS

BEGIN

	DECLARE @questionnaireIds Table (Value INT) 

	insert into @questionnaireIds
	select cast(value as int) from GetTableOfDelimittedString(@ids, ',') 

	--delete answers
	delete from answer where questionnaireanswerid in 
	(select id from questionnaireanswer where questionnaireversionid in 
	(select id from questionnaireversion where questionnaireid in (select value from @questionnaireIds)))
	IF @@ERROR <> 0
	BEGIN
		RAISERROR('Unable to Delete Answers', 16, 1)
		RETURN
	END

	--store coi ids
	DECLARE @TempCOIIds TABLE(
	[COIId] [int] NOT NULL
	)

	insert into @TempCOIIds
	select Id From ConflictOfInterest COI
		INNER JOIN COI_QuestionnaireAnswer_Map Map ON COI.Id = Map.COIId
	WHERE Map.QuestionnaireAnswerId in (
	select Id from QuestionnaireAnswer where questionnaireversionid in 
	(select id from questionnaireversion where questionnaireid in (select value from @questionnaireIds)))
	IF @@ERROR <> 0
	BEGIN
		RAISERROR('Unable to Insert coi ids', 16, 1)
		RETURN
	END

	--delete coi_questionnaireanswer_map
	delete from coi_questionnaireanswer_map where questionnaireanswerid in  (
	select Id from QuestionnaireAnswer where questionnaireversionid in 
	(select id from questionnaireversion where questionnaireid in (select value from @questionnaireIds)))

	--delete coi
	delete from conflictofinterest where id in (select coiid from @TempCOIIds)

	delete from questionnaireanswer where questionnaireversionid in 
	(select id from questionnaireversion where questionnaireid in (select value from @questionnaireIds))
	IF @@ERROR <> 0
	BEGIN
		RAISERROR('Unable to Delete QuestionnaireAnswers', 16, 1)
		RETURN
	END
	
	delete from itemproperty where itemid in 
	(select id from questionnaireitem where versionid in 
	(select id from questionnaireversion where questionnaireid in (select value from @questionnaireIds)))
	IF @@ERROR <> 0
	BEGIN
		RAISERROR('Unable to Delete ItemProperties', 16, 1)
		RETURN
	END
	
	delete from itemoption where itemid in 
	(select id from questionnaireitem where versionid in 
	(select id from questionnaireversion where questionnaireid in (select value from @questionnaireIds)))
	IF @@ERROR <> 0
	BEGIN
		RAISERROR('Unable to Delete ItemOptions', 16, 1)
		RETURN
	END
	
	delete from questionnaireitem where versionid in 
	(select id from questionnaireversion where questionnaireid in (select value from @questionnaireIds))
	IF @@ERROR <> 0
	BEGIN
		RAISERROR('Unable to Delete QuestionnaireItems', 16, 1)
		RETURN
	END

	DECLARE @TempScriptIds TABLE(
	[ScriptId] [int] NOT NULL
	)

	insert into @TempScriptIds
	select ScriptId from script_questionnaireversion_map where questionnaireversionid in 
	(select id from questionnaireversion where questionnaireid in (select value from @questionnaireIds))
	IF @@ERROR <> 0
	BEGIN
		RAISERROR('Unable to Insert script ids', 16, 1)
		RETURN
	END
	
	delete from script_questionnaireversion_map where questionnaireversionid in 
	(select id from questionnaireversion where questionnaireid in (select value from @questionnaireIds))
	IF @@ERROR <> 0
	BEGIN
		RAISERROR('Unable to Delete Mapping entries', 16, 1)
		RETURN
	END
	
	delete from script where id in (select scriptid from @TempScriptIds)
	IF @@ERROR <> 0
	BEGIN
		RAISERROR('Unable to Delete SCripts', 16, 1)
		RETURN
	END
	
	delete from questionnaireversion where questionnaireid in (select value from @questionnaireIds)
	IF @@ERROR <> 0
	BEGIN
		RAISERROR('Unable to Delete QuestionnaireVersions', 16, 1)
		RETURN
	END

	delete from questionnaire where id in (select value from @questionnaireIds)
	IF @@ERROR <> 0
	BEGIN
		RAISERROR('Unable to Delete Questionnaires', 16, 1)
		RETURN
	END
		
		
END

GO
